use sqlx::SqlitePool;

use crate::utils;

#[derive(Debug, sqlx::FromRow)]
pub struct MpUser {
    pub id: i64,
    pub openid: String,
    pub nickname: String,
    pub createtime: i64,
    pub updatetime: i64,
}

// 创建用户
pub async fn create_user(
    pool: &SqlitePool,
    openid: String,
    nickname: String,
) -> anyhow::Result<()> {
    let ts = utils::get_timestamp();
    // 这是使用fomat构造sql语句，如果从客户端传入，需要防范sql注入
    let add_user_sql = format!(
        "insert into mp_users(openid,nickname,createtime,updatetime) values ('{}','{}',{},{})",
        openid, nickname, ts, ts
    );
    sqlx::query(&add_user_sql).execute(pool).await?;
    Ok(())
}

pub async fn remove_user(pool: &SqlitePool, openid: String) -> anyhow::Result<()> {
    let remove_user_sql = format!("delete from mp_users where openid = '{}'", openid);
    sqlx::query(&remove_user_sql).execute(pool).await?;
    Ok(())
}

// 查询用户，根据openid
pub async fn query_user(pool: &SqlitePool, openid: String) -> anyhow::Result<MpUser> {
    // let mut conn = pool.acquire().await?;
    let user = sqlx::query_as::<_, MpUser>("SELECT * FROM mp_users where openid = ?")
        .bind(openid)
        .fetch_one(pool)
        .await?;

    Ok(user)
}
// 可查看记录条数，判断用户记录是否存在
pub async fn query_user_count(pool: &SqlitePool, openid: String) -> anyhow::Result<i64> {
    let row: (i64,) = sqlx::query_as("select count(*) from mp_users where openid = ?")
        .bind(openid)
        .fetch_one(pool)
        .await?;
    Ok(row.0)
}

// 更新用户昵称
pub async fn update_user_nickname(
    pool: &SqlitePool,
    openid: String,
    nickname: String,
) -> anyhow::Result<()> {
    sqlx::query("update mp_users set nickname = ? where openid = ?")
        .bind(nickname)
        .bind(openid)
        .execute(pool)
        .await?;
    Ok(())
}

#[derive(Debug, sqlx::FromRow)]
pub struct MpBook {
    pub id: i64,
    pub openid: String,
    pub name: String,
    pub press: String,
    pub isbn: String,
    pub isshare: i32,
    pub status: i32,
    pub createtime: i64,
    pub updatetime: i64,
}

pub async fn add_book(
    pool: &SqlitePool,
    openid: String,
    name: String,
    press: String,
    isbn: String,
    isshare: i32,
) -> anyhow::Result<()> {
    let ts = utils::get_timestamp();
    // 这是使用fomat构造sql语句，如果从客户端传入，需要防范sql注入

    let add_book_sql =
        "insert into mp_books(openid,name,press,isbn,isshare,status,createtime,updatetime) values (?,?,?,?,?,0,?,?)";
    sqlx::query(&add_book_sql)
        .bind(openid)
        .bind(name)
        .bind(press)
        .bind(isbn)
        .bind(isshare)
        .bind(ts)
        .bind(ts)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn delete_book(pool: &SqlitePool, openid: String, name: String) -> anyhow::Result<()> {
    let delete_book_sql = "delete from mp_books where openid = ? and name = ?";
    sqlx::query(&delete_book_sql)
        .bind(openid)
        .bind(name)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn delete_book_by_id(pool: &SqlitePool, openid: String, id: i64) -> anyhow::Result<()> {
    let delete_book_sql = "delete from mp_books where openid = ? and id = ?";
    sqlx::query(&delete_book_sql)
        .bind(openid)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn share_book_by_id(pool: &SqlitePool, openid: String, id: i64) -> anyhow::Result<()> {
    let delete_book_sql = "update mp_books set isshare = 1 where openid = ? and id = ?";
    sqlx::query(&delete_book_sql)
        .bind(openid)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

// 查询图书列表，根据openid
pub async fn get_my_book_list(
    pool: &SqlitePool,
    openid: String,
    page: i32,
    page_size: i32,
) -> anyhow::Result<Vec<MpBook>> {
    let page = if page <= 0 { 1 } else { page };
    let page_size = if page_size <= 0 { 5 } else { page_size };
    // let mut conn = pool.acquire().await?;
    let books = sqlx::query_as::<_, MpBook>(
        "SELECT * FROM mp_books where openid = ? order by id desc limit ? offset ? ",
    )
    .bind(openid)
    .bind(page_size)
    .bind((page - 1) * page_size)
    .fetch_all(pool)
    .await?;

    Ok(books)
}

pub async fn get_pub_book_list(
    pool: &SqlitePool,
    page: i32,
    page_size: i32,
) -> anyhow::Result<Vec<MpBook>> {
    let page = if page <= 0 { 1 } else { page };
    let page_size = if page_size <= 0 { 5 } else { page_size };

    // let mut conn = pool.acquire().await?;
    let books = sqlx::query_as::<_, MpBook>(
        "SELECT * FROM mp_books where isshare = 1 order by id desc limit ? offset ? ",
    )
    .bind(page_size)
    .bind((page - 1) * page_size)
    .fetch_all(pool)
    .await?;

    Ok(books)
}

pub async fn get_book_detail(pool: &SqlitePool, id: i64) -> anyhow::Result<MpBook> {
    let book = sqlx::query_as::<_, MpBook>("SELECT * FROM mp_books where id = ? ")
        .bind(id)
        .fetch_one(pool)
        .await?;

    Ok(book)
}

pub async fn get_book_by_isbn(pool: &SqlitePool, isbn: String) -> anyhow::Result<MpBook> {
    let book = sqlx::query_as::<_, MpBook>("SELECT * FROM mp_books where isbn = ? ")
        .bind(isbn)
        .fetch_one(pool)
        .await?;

    Ok(book)
}
// 根据isbn和openid查询图书的数量
pub async fn query_book_count_v1(
    pool: &SqlitePool,
    openid: String,
    isbn: String,
) -> anyhow::Result<i64> {
    let row: (i64,) = sqlx::query_as("select count(*) from mp_books where openid = ? and isbn = ?")
        .bind(openid)
        .bind(isbn)
        .fetch_one(pool)
        .await?;
    Ok(row.0)
}

// 审核，根据ISBN查找
pub async fn audit_book_by_isbn(
    pool: &SqlitePool,
    isbn: String,
    status: i32,
) -> anyhow::Result<()> {
    let audit_book_sql = "update mp_books set status = ? where isbn = ?";
    sqlx::query(&audit_book_sql)
        .bind(status)
        .bind(isbn)
        .execute(pool)
        .await?;
    Ok(())
}

#[derive(Debug, sqlx::FromRow)]
pub struct AppAccount {
    pub id: i64,
    pub openid: String,
    pub appid: String,
    pub appsecret: String,
    pub status: i32,
    pub createtime: i64,
    pub updatetime: i64,
}
// 添加账号
pub async fn add_appaccount(
    pool: &SqlitePool,
    openid: String,
    appid: String,
    appsecret: String,
) -> anyhow::Result<()> {
    let ts = utils::get_timestamp();
    // 这是使用fomat构造sql语句，如果从客户端传入，需要防范sql注入
    let md5_appsecret = utils::md532(&appsecret);
    let add_sql = format!(
        "insert into app_accounts(openid,appid,appsecret,status,createtime,updatetime) values ('{}','{}','{}',{},{},{})",
        openid, appid,md5_appsecret,1, ts, ts
    );
    sqlx::query(&add_sql).execute(pool).await?;
    Ok(())
}
// 获取用户账户
pub async fn get_appaccount_by_appid(
    pool: &SqlitePool,
    appid: String,
) -> anyhow::Result<AppAccount> {
    let data = sqlx::query_as::<_, AppAccount>("SELECT * FROM app_accounts where appid = ? ")
        .bind(appid)
        .fetch_one(pool)
        .await?;

    Ok(data)
}
pub async fn get_appaccount_by_openid(
    pool: &SqlitePool,
    openid: String,
) -> anyhow::Result<AppAccount> {
    let data = sqlx::query_as::<_, AppAccount>("SELECT * FROM app_accounts where openid = ? ")
        .bind(openid)
        .fetch_one(pool)
        .await?;

    Ok(data)
}

pub async fn reset_appaccount_secret_by_appid(
    pool: &SqlitePool,
    appid: String,
    appsecret: String,
) -> anyhow::Result<()> {
    let md5_appsecret = utils::md532(&appsecret);
    let sql = "update app_accounts set appsecret = ? where appid = ?";
    sqlx::query(&sql)
        .bind(md5_appsecret)
        .bind(appid)
        .execute(pool)
        .await?;
    Ok(())
}